﻿-- =============================================
-- Author:		zengfei	
-- Create date: 10m/14/2011
-- Description:	生成Serial NO.
-- =============================================
CREATE PROCEDURE [dbo].[Usp_SerialNo]
	@p_SerialNoName	sysname,
	@p_FieldName	sysname,
	@p_TableName	sysname,		--表名
	@p_PrefixTime	varchar(8),	    --時間：20111014
	@p_RSerialNo    varchar(30) out
as
declare @Id	int,
		@OldSerialNo varchar(30),
		@ParamList varchar(500)
		
select @Id=[Id],@OldSerialNo=SerialNo 
	from MaxSerialNo
	where FieldName=@p_FieldName and TableName=@p_TableName and [Date]=@p_PrefixTime
	
if @OldSerialNo is null
begin
	select @ParamList=
'<Root>
	<Param>
		<Name>@p_PrefixTime</Name>
		<Value>'+@p_PrefixTime+'</Value>
	</Param>
	<Param>
		<Name>@p_TableName</Name>
		<Value>'+@p_TableName+'</Value>
	</Param>
</Root>'
	exec Usp_SerialNoFromSetting @p_SerialNoName,@ParamList,null,@p_RSerialNo out
	
	insert MaxSerialNo select @p_PrefixTime,@p_TableName,@p_RSerialNo,@p_FieldName
end
else
begin
	update M set @p_RSerialNo=SerialNo=left(SerialNo,PosStart-1)+
		right(replicate(Flag,PosEnd-PosStart+1)+cast(cast(substring(SerialNo,PosStart,PosEnd-PosStart+1) as int)+1  as varchar),PosEnd-PosStart+1)+
		right(SerialNo,datalength(SerialNo)-PosEnd)
	from MaxSerialNo M join SerialNoSetting C on C.SerialNoName=@p_SerialNoName and C.Type='AUTO'
	where M.[Id]=@Id
end
return